package com.example.demo.repository;

import com.example.demo.entity.Book;
import com.example.demo.entity.Category;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface BookRepository extends JpaRepository<Book, Long> {

    //自己添加需要的
    //方式1：查询方法名使用JPA的命名规则（功能会自动实现）
    List<Book> findByNameLikeOrderByIdDesc(String name);


    //方式2：使用JPQL语言
//    @Transactional
//    @Modifying
//    @Query("update Book b set b.name = ?1, b.category = ?2,b.author=?3,b.isbn=?4,b.price=?5,b.publish=?6 where b.id = ?7")
//    int updateBook(String name, Category category,String author,String isbn,double price,String publish, Long book_id);
    //方式3：使用原生的sql语言


    @Transactional
    @Modifying
    @Query("update Book b set b.name = ?1, b.category = ?2,b.author=?3,b.isbn=?4,b.price=?5,b.publish=?6 where b.id = ?7")
    int updateBook(String name, Category category, String author, String isbn, double price, String publish, Long book_id);

    @Transactional
    @Modifying
    @Query(value = "delete from book where name =?1", nativeQuery = true)
    int deleteByName(String name);

    @Transactional
    @Modifying
    @Query(value = "insert into book set id =?1,isbn=?2,name=?3,author=?4,publish=?5,price=?6,category_id=?7", nativeQuery = true)
    int addbook(Long book_id,String isbn,String name, String author,String publish,double price,Long category_id);

    @Transactional
    @Modifying
    @Query(value = "SELECT *FROM book WHERE publish like concat('%',?1,'%')", nativeQuery = true)
    List<Book> findbookbypub(String publish);



    @Transactional
    @Modifying
    @Query(value = "SELECT *\n" +
            "FROM book  INNER JOIN category \n" +
            "ON book.category_id=category.id\n" +
            "AND category.name LIKE concat('%',?1,'%');", nativeQuery = true)
    List<Book> findbookbycat(String cat);





}
